﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace festivalDatabase
{
    public partial class editMusicianSchedule : userInterface
    {
        int oldPerfTime;
        string oldMusician, oldPerfDate;
        /// <summary>
        /// Constructor for the Musician Schedule
        /// </summary>
        /// <param name="db"></param>
        /// <param name="festivalID"></param>
        public editMusicianSchedule(dbAccessor db, int festivalID)
        {
            InitializeComponent();
            setIcon();
            dbAccess = db;
            fillMusicians();
            currentFestivalID = festivalID;
            fillStageNames();
            addTimeSlotRadioButton.Checked = true;

            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT name, start_date, end_date FROM festival WHERE id=@festID;";
            SQLiteParameter param = new SQLiteParameter("@festID", currentFestivalID);
            query.Parameters.Add(param);
            DataTable dt = dbAccess.executeQuery(query);
            festivalTextBox.Text = dt.Rows[0]["name"].ToString() + ", " + currentFestivalID.ToString();
            string start = formatDateStringForDisplay(dt.Rows[0]["start_date"].ToString());
            dateTextBox.Text = start;
            if (start.Length > 0)
            {
                start = start.Insert(2, "/");
                start = start.Insert(5, "/");
            }
            startDateLabel.Text += start;
            string end = formatDateStringForDisplay(dt.Rows[0]["end_date"].ToString());
            if (end.Length > 0)
            {
                end = end.Insert(2, "/");
                end = end.Insert(5, "/");
            }
            endDateLabel.Text += end;
        }

        /// <summary>
        /// Fills the musician combo box with valid musicians
        /// </summary>
        void fillMusicians()
        {
            SQLiteCommand query = dbAccess.getCommand();

            query.CommandText = "SELECT musician_name " +
                                "FROM musician;";

            try
            {
                DataTable dt = dbAccess.executeQuery(query);

                foreach (DataRow row in dt.Rows)
                {
                    musicianComboBox.Items.Add((row["musician_name"].ToString()));
                }
            }
            catch
            {
                // do nothing
            }
        }

        private void fillStageNames()
        {
            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT stage_name " +
                                "FROM music_festival_has_stage " +
                                "WHERE festival_id=@festID;";
            SQLiteParameter param = new SQLiteParameter("@festID", currentFestivalID);
            query.Parameters.Add(param);

            try
            {
                DataTable dt = dbAccess.executeQuery(query);

                foreach (DataRow row in dt.Rows)
                {
                    stageComboBox.Items.Add(row["stage_name"].ToString());
                }
            }
            catch
            {
                // do nothing
            }
        }

        /// <summary>
        /// Displays the current schedule for the indicated stage.
        /// </summary>
        /// <param name="stageName"></param>
        void updateTimeSlots()
        {
            stageScheduleListBox.Items.Clear();

            SQLiteCommand query = dbAccess.getCommand();

            query.CommandText = "SELECT * " + //musician_name, date, time " + 
                                "FROM musician_plays " +
                                "WHERE stage_name=@stageName AND festival_id=@festID AND date=@date " +
                                "ORDER BY time";
            SQLiteParameter param = new SQLiteParameter("@stageName", stageComboBox.Text.ToString());
            query.Parameters.Add(param);
            param = new SQLiteParameter("@festID", currentFestivalID);
            query.Parameters.Add(param);
            param = new SQLiteParameter("@date", formatGenericDate(dateTextBox.Text.ToString()));
            query.Parameters.Add(param);

            try
            {
                DataTable dt = dbAccess.executeQuery(query);

                foreach (DataRow row in dt.Rows)
                {
                    string date = formatDateStringForDisplay(row["date"].ToString());
                    if (date.Length != 0)
                    {
                        date = date.Insert(2, "/");
                        date = date.Insert(5, "/");
                    }
                    stageScheduleListBox.Items.Add(row["musician_name"].ToString() + ", " + date + " " + row["time"].ToString() + ":00:00");
                }
            }
            catch
            {
                // do nothing
            }
        }

        /// <summary>
        /// When changes are submitted, validate input, check which radio button is selected,
        /// and call a method add, remove, or edit accordingly.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void submitButton_Click(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            if (dateIsValid())
            {
                if (addTimeSlotRadioButton.Checked)
                {
                    if (keysAreUnique())
                        addTimeSlot();
                    else
                        MessageBox.Show("Time slot already filled for this festival/stage combination");
                }
                else if (editTimeSlotRadioButton.Checked)
                {
                    if (keysAreUnique() || (dateTextBox.Text.ToString() == oldPerfDate && oldPerfTime == Int32.Parse(performanceTime.Text.ToString())))
                        editTimeSlot();
                }
                else
                    deleteTimeSlot();
            }
            else
                errorProvider1.SetError(dateTextBox, "Date is not during current festival");
        }

        private void addTimeSlot()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "INSERT INTO musician_plays " +
                                   "VALUES (@festID, @name, @stage, @date, @time);";
            SQLiteParameter param = new SQLiteParameter("@festID", currentFestivalID);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@name", musicianComboBox.Text.ToString());
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@stage", stageComboBox.Text.ToString());
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@date", formatGenericDate(dateTextBox.Text.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@time", Int32.Parse(performanceTime.Text.ToString()));
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);

            updateTimeSlots();
            MessageBox.Show("Time slot added");
        }

        private void editTimeSlot()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "UPDATE musician_plays " +
                                   "SET musician_name=@name, date=@date, time=@time " +
                                   "WHERE festival_id=@festID AND stage_name=@stage AND date=@oldDate AND time=@oldTime;";
            SQLiteParameter param = new SQLiteParameter("@name", musicianComboBox.Text.ToString());
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@date", formatGenericDate(dateTextBox.Text.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@time", Int32.Parse(performanceTime.Text.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@festID", currentFestivalID);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@stage", stageComboBox.Text.ToString());
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@oldDate", oldPerfDate);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@oldTime", oldPerfTime);
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);

            updateTimeSlots();
            MessageBox.Show("Entry updated");
        }

        private void deleteTimeSlot()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE FROM musician_plays " +
                                   "WHERE festival_id=@festID AND stage_name=@stage " +
                                   "AND date=@date AND time=@time;";
            SQLiteParameter param = new SQLiteParameter("@festID", currentFestivalID);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@stage", stageComboBox.Text.ToString());
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@date", formatGenericDate(dateTextBox.Text.ToString()));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@time", Int32.Parse(performanceTime.Text.ToString()));
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);

            updateTimeSlots();
            MessageBox.Show("Entry deleted");
        }

        private bool keysAreUnique()
        {
            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT time " +
                                "FROM musician_plays " +
                                "WHERE festival_id=@festID " +
                                "AND date=@date " +
                                "AND stage_name=@stage;";
            SQLiteParameter param = new SQLiteParameter("@festID", currentFestivalID);
            query.Parameters.Add(param);
            param = new SQLiteParameter("@date", formatGenericDate(dateTextBox.Text.ToString()));
            query.Parameters.Add(param);
            param = new SQLiteParameter("@stage", stageComboBox.Text.ToString());
            query.Parameters.Add(param);

            DataTable dt = dbAccess.executeQuery(query);

            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    if (Int32.Parse(row["time"].ToString()) == Int32.Parse(performanceTime.Text.ToString()))
                        return false;
                }
                return true;
            }
            catch
            {
                return true;
            }
        }

        bool dateIsValid()
        {
            SQLiteCommand query = dbAccess.getCommand();

            // Gets festival information regarding start_date and end_date
            query.CommandText = "SELECT start_date, end_date FROM festival WHERE id=@FestID;";
            query.Parameters.Add("@FestID", DbType.Int16);
            query.Parameters["@FestId"].Value = currentFestivalID;

            DateTime startDate;
            DateTime endDate;
            DataTable dt = dbAccess.executeQuery(query);
            DataRow row = dt.Rows[0];

            try
            {
                startDate = DateTime.Parse(row["start_date"].ToString());
            }
            catch
            {
                startDate = new DateTime(1000, 01, 01);
            }

            try
            {
                endDate = DateTime.Parse(row["end_date"].ToString());
            }
            catch
            {
                endDate = new DateTime(9999, 12, 31);
            }

            DateTime inputTime = DateTime.Parse(formatGenericDate(dateTextBox.Text));

            if (startDate <= inputTime && inputTime <= endDate)
            {
                return true;
            }

            return false;
        }

        private void addTimeSlotRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            submitButton.Text = "Add To Schedule";
            clearFields();
            enableFields(true);
        }

        private void editTimeSlotRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            submitButton.Text = "Edit Entry";
            clearFields();
            enableFields(true);
        }

        private void removeTimeSlotRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            submitButton.Text = "Remove Entry";
            clearFields();
            enableFields(false);
        }

        private void clearFields()
        {
            stageScheduleListBox.ClearSelected();
            performanceTime.Text = "12";
            musicianComboBox.Text = "";
        }

        private void enableFields(bool maybe)
        {
            musicianComboBox.Enabled = maybe;
            dateTextBox.Enabled = true;
            performanceTime.Enabled = maybe;
        }

        private void stageComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            updateTimeSlots();
        }

        private void stageScheduleListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (addTimeSlotRadioButton.Checked)
                return;
            else
            {
                try
                {
                    oldPerfDate = dateTextBox.Text;
                    string selected = stageScheduleListBox.SelectedItem.ToString();
                    string perfTime = selected.Substring(selected.Length - 8, 2);
                    if (perfTime.IndexOf(' ') > -1)
                    {
                        perfTime.Substring(1);
                        string name = selected.Substring(0, selected.Length - 20);
                        musicianComboBox.Text = name;
                        oldMusician = name;
                    }
                    else
                    {
                        string name = selected.Substring(0, selected.Length - 21);
                        musicianComboBox.Text = name;
                        oldMusician = name;
                    }
                    performanceTime.Text = Int32.Parse(perfTime).ToString();
                    oldPerfTime = Int32.Parse(performanceTime.Text.ToString());
                }
                catch
                {
                    // do nothing
                }
            }
        }
    }
}
